/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package neo.workforce.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import neo.workforce.dao.Workspace;
import neo.workforce.dao.WorkspaceDAO;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author KTB_User
 */
public class WorkspaceDAOImpl implements WorkspaceDAO {

    private JdbcTemplate jdbcTemplate;
    private Logger log = Logger.getLogger(this.getClass());
    
    public WorkspaceDAOImpl() {
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Workspace findWorkspace(long userId, long id) {
        final StringBuilder sql = new StringBuilder();
        sql.append("SELECT ID, SESSION_ID, PROCESS_ID, ACTIVITY_ID, ");
        sql.append("INCOMMING, OPEN_JOB, CLOSE_JOB FROM U").append(userId).append("_WORKSPACE WHERE ID = ").append(id);
        log.info("SQL:"+ sql.toString());
        Workspace workspace = null;
        try {
            workspace = (Workspace)jdbcTemplate.queryForObject(sql.toString(), new WorkspaceRowMapper());
            return workspace;
        } catch(Exception ex) {
            log.info("findWorkspace Error:", ex);
            return null;
        }
    }
    
    public List findWorkspace(long userId, boolean inComming, boolean openJob, boolean closeJob) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ID, SESSION_ID, PROCESS_ID, ACTIVITY_ID, INCOMMING, OPEN_JOB, CLOSE_JOB ");
        sql.append("FROM ").append("U").append(userId).append("_WORKSPACE ");
        sql.append("WHERE ");
        if (inComming == true) {
            sql.append(" INCOMMING is not null ");
        } else {
            sql.append(" INCOMMING is null ");
        }
        sql.append(" AND ");
        if (openJob == true) {
            sql.append(" OPEN_JOB is not null ");
        } else {
            sql.append(" OPEN_JOB is null ");
        }
        sql.append(" AND ");
        if (closeJob == true) {
            sql.append(" CLOSE_JOB is not null ");
        } else {
            sql.append(" CLOSE)JOB is null ");
        }
        log.info("SQL:"+ sql.toString());
        List rows = null;
        try {
            rows = jdbcTemplate.queryForList(sql.toString(), new WorkspaceRowMapper(), new Object[]{});
        } catch(Exception ex) {
            log.info("find Workspace:", ex);
            rows = new ArrayList();
        }
        return rows;
    }

    public long saveWorkspace(long uid, final Workspace workspace) {
        final StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(uid).append("_WORKSPACE ");
        sql.append("(SESSION_ID, PROCESS_ID, ACTIVITY_ID, INCOMMING, OPEN_JOB, CLOSE_JOB)");
        sql.append(" VALUES(?,?,?,?,?,?)");
        log.info("SQL:"+ sql.toString());
        KeyHolder keyHolder = new GeneratedKeyHolder();
        try {
            jdbcTemplate.update(
                    new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps =
                            connection.prepareStatement(sql.toString());
                    ps.setString(1, workspace.getSessionId());
                    ps.setLong(2, workspace.getProcessId());
                    ps.setLong(3, workspace.getActivityId());
                    ps.setDate(4, new java.sql.Date(workspace.getIncomming().getTime()));
                    ps.setDate(5, new java.sql.Date(workspace.getOpenJob().getTime()));
                    ps.setDate(6, new java.sql.Date(workspace.getCloseJob().getTime()));
                    return ps;
                }
            }, keyHolder);
            return keyHolder.getKey().longValue();
        } catch (Exception ex) {
            log.info("save Workspace uid:"+ uid, ex);
            return -1;
        }
    }
    
    public boolean updateWorkspace(long uid, final Workspace workspace) {
        final StringBuilder sql = new StringBuilder();
        sql.append("UPDATE U").append(uid).append("_WORKSPACE SET ");
        sql.append(" SESSION = ?, PROCESS_ID = ?, ACTIVITY_ID = ?, ");
        sql.append(" INCOMMING = ?, OPEN_JOB = ?, CLOSE_JOB = ? ");
        sql.append(" WHERE ID = ?");
        try {
            jdbcTemplate.update(sql.toString(), new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps =
                            connection.prepareStatement(sql.toString());
                    ps.setString(1, workspace.getSessionId());
                    ps.setLong(2, workspace.getProcessId());
                    ps.setLong(3, workspace.getActivityId());
                    ps.setDate(4, new java.sql.Date(workspace.getIncomming().getTime()));
                    ps.setDate(5, new java.sql.Date(workspace.getOpenJob().getTime()));
                    ps.setDate(6, new java.sql.Date(workspace.getCloseJob().getTime()));
                    ps.setLong(7, workspace.getId());
                    return ps;
                }
            });
            return true;
        } catch(Exception ex) {
            log.info("update Workspace:", ex);
            return false;
        }
    }
    
}
